/*
 * Copyright 2007-208 the original author or authors.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */ 
package com.cassevern.storproc.util

/* sample use:
	new com.cassevern.storproc.util.BuildStorProcFromPCML (ip:'172.16.1.4', user:'DGD', password:'CHANGE', library:'dgd', storProcName:'inoutparms', pcmlName:'C:/getacnts.pcml').run()
*/

class BuildStorProcFromPCML {
	def ip
	def user
	def password
	def library
	def storProcName 
	def pcmlName
	
	String run () {
		println "Processing ${pcmlName} into a stored prodecure..."
		def pcml = new XmlSlurper().parse(new File("${pcmlName}"))
		
		def progName = pcml.program[0].@name
		def entryPoint = pcml.program[0].@entrypoint
		def path = pcml.program[0].@path
		def storProcParams = ''
		
		pcml.program[0].data.each { parm ->
			switch (parm.@usage) {
			case 'input':       storProcParams <<= 'IN ';    break; 		
			case 'output':      storProcParams <<= 'OUT ';   break; 		
			case 'inputoutput': storProcParams <<= 'INOUT '; break; 	
			}
			storProcParams <<= parm.@name
			switch (parm.@type) {
			case 'packed': 
				storProcParams <<= " DECIMAL(${parm.@length},${parm.@precision})"
				break
			case 'zoned':  
				storProcParams <<= " NUMERIC(${parm.@length},${parm.@precision})"
				break
			case 'char':   
				storProcParams <<= " CHAR(${parm.@length})"
				break
			default: println "ERROR: type ${parm.@type} for ${parm.@name} not handled"
			}
			storProcParams <<= ", "
		}
		storProcParams = storProcParams.toString().replaceAll(/, $/, '') //	remove trailling commas
		
		def matcher = pcmlName =~ /.*\/([_a-zA-Z0-9]*)\.[_a-zA-Z0-9]*$/
		def rpgName = matcher[0][1]
		def storProc =  
			"""
			CREATE PROCEDURE ${library}.${storProcName}
			($storProcParams)
			LANGUAGE RPGLE
			PARAMETER STYLE GENERAL
			NOT DETERMINISTIC
			CONTAINS SQL 
			DYNAMIC RESULT SETS 1
			SPECIFIC ${library}.$storProcName 
			NEW SAVEPOINT LEVEL
			EXTERNAL NAME $library.$rpgName
			"""
		storProc = storProc.toString().replaceAll(/\r/, ' ')	
		println storProc
		
		def sql = groovy.sql.Sql.newInstance("jdbc:as400://$ip/library", user, password, "com.ibm.as400.access.AS400JDBCDriver")
		try {
			sql.execute storProc
		} catch (java.sql.SQLException e)	{
			println("WARN: ${e.toString()}. Dropping and recreating...")
			if (e.getErrorCode() == -454) {
				def sqlRtn = sql.getConnection().createStatement().executeUpdate("drop procedure ${library}.${storProcName} ")
				if (sqlRtn) println("ERROR: drop procedure return code: $sqlRtn")
				sql.getConnection().createStatement().executeUpdate(storProc)
			}
		}
		storProc.toString().replaceAll(/\t/, '')
		storProc.toString().replaceAll(/\n/, '')
	}
}